import csv
import pymysql
import os
import time


# 将天气数据以csv格式存储，注：风力等级如3-4在wps中会被转换成3月4日，不是数据问题，是wps自己的问题！！！
def save_as_csv(city):
    global csv_change_count

    # 判断文件路径是否存在
    folder_path = 'city/'
    if not os.path.exists(folder_path):
        os.mkdir(folder_path)

    file_path = folder_path + city + '.csv'
    # 如果文件不存在，直接写入数据，否则需判断该城市今天的天气数据是否已存在
    if not os.path.exists(file_path):
        with open(file_path, 'w', newline='') as f:
            csv_writer = csv.writer(f)
            csv_writer.writerow(('日期', '昼夜', '天气', '最低温', '最高温', '风力', '风向'))
            csv_writer.writerows(weather[city])
            csv_change_count += len(weather[city])
    else:
        f = open(file_path, 'r')
        data = csv.reader(f)
        data2 = []
        for i in data:
            if i:
                data2.append(i)
        f.close()
        data3 = data2[1:]
        data4 = {}
        for i in data3:
            if i[0] in data4:
                if i[1] not in data4[i[0]]:
                    data4[i[0]].append(i[1])
            else:
                data4[i[0]] = [i[1]]

        f2 = open(file_path, 'a', newline='')
        csv_writer = csv.writer(f2)
        for i in weather[city]:
            if i[0] in data4 and i[1] in data4[i[0]]:
                continue
            csv_writer.writerow(i)
            csv_change_count += 1
        f2.close()
    print(city, '数据以csv格式操作成功')


# 将天气数据以mysql形式存储
def save_as_mysql(city):
    global mysql_change_count

    if city in tables:
        data = []
        sql2 = 'select date, daynight from %s' % city
        cursor.execute(sql2)
        result2 = cursor.fetchall()
        for i in result2:
            data.append((i[0], i[1]))

        for i in weather[city]:
            if (i[0], i[1]) in data:
                continue
            sql3_2 = 'insert into %s ' % city
            sql3 = sql3_2 + 'values ("%s", "%s", "%s", %s, %s, "%s", "%s")' % i
            try:
                cursor.execute(sql3)
                db.commit()
                mysql_change_count += 1
            except:
                db.rollback()

    else:
        sql4 = 'create table %s (date varchar(10) not null, daynight varchar(8) not null, weather varchar(10) not null, min_temp smallint not null, max_temp smallint not null, wind_power varchar(8), wind_direction varchar(8))' % city
        cursor.execute(sql4)

        sql5 = 'insert into %s ' % city
        sql6 = sql5 + 'values (%s, %s, %s, %s, %s, %s, %s)'
        try:
            cursor.executemany(sql6, weather[city])
            db.commit()
            mysql_change_count += len(weather[city])
        except:
            db.rollback()

    print(city, '数据以mysql格式操作成功')


def main():
    for i in weather.keys():
        save_as_csv(i)
        save_as_mysql(i)


if __name__ == '__main__':
    start = time.time()
    csv_change_count = 0
    mysql_change_count = 0
    db = pymysql.connect(host='localhost', user='root', password='123456', database='city_weather')
    cursor = db.cursor()
    # 首先判断数据库中是否存在该表格
    sql = 'show tables'
    tables = []
    cursor.execute(sql)
    result = cursor.fetchall()
    for i in result:
        tables.append(i[0])

    with open('temp_weather.txt', 'r') as f:
        data = f.read()
        weather = eval(data)

    main()
    db.close()
    print('csv文件共插入数据：', csv_change_count, '条')
    print('mysql文件共插入数据：', mysql_change_count, '条')
    print('\n总用时：', time.time()-start)
